# from util.Sql_df import SQLServer_df
# from util.sql_pyodbc import SQLServer_pyodbc
from api import sqlpyodbc,sqldf
# 获取所有角色
def GetRoles():
    sql = f'SELECT role_id,role_name FROM roles'
    try:
        return sqldf.Query(sql)
    except:
        return None

#  获取所有用户角色
def GetUser_roles(queryname):
    sql = f'''
        WITH UserRoles AS (
    SELECT
        UserID,
        STUFF((SELECT ',' + role_name FROM user_roles a1
               LEFT JOIN roles b1 ON a1.role_id = b1.role_id
               WHERE a1.UserID = a.UserID
               FOR XML PATH('')), 1, 1, '') AS roles
    FROM
        user_roles AS a
    GROUP BY
        UserID
)
SELECT
    u.UserID,
    u.UserNumber,
    u.UserName,
    ur.roles
FROM
    UserManagement AS u
    LEFT JOIN UserRoles AS ur ON u.UserID = ur.UserID WHERE u.UserName LIKE '%{queryname}%';
    '''
    try:
        return sqldf.Query(sql)
    except:
        return None

# 添加用户
def Add_user(username,fullname,pwd):
    sql = f'''
        INSERT INTO UserManagement (UserNumber,UserName,UserPwd)
        VALUES ('{username}','{fullname}','{pwd}');
    '''
    try:
        return sqlpyodbc.execute(sql)
    except:
        return None

# 删除用户
def Delete_user(UserID):
    sqlList=[]
    sqlList.append(f'DELETE FROM UserManagement WHERE UserID={UserID}')
    sqlList.append(f'DELETE FROM user_roles WHERE UserID={UserID}')
    try:
        return sqlpyodbc.execute_transaction(sqlList)
    except:
        return False

# 添加用户角色
def Add_user_roles(sqlList):
    try:
        return sqlpyodbc.execute_transaction(sqlList)
    except:
        return False

        
